﻿using System;
using System.Collections.Generic;
using System.Configuration;
using System.Data;
using System.Data.SqlClient;
using System.Reflection;
using System.Reflection.Emit;
using System.Transactions;

namespace TypeBuilderFx.Core.Emitters
{
    /// <summary>Emits a class implementing a data access component (DAC) calling SQL Server stored procedures.</summary>
    public class SqlDacEmitter : TypeEmitterBase<SqlDacOptions>
    {
        private const string CONNECTION_FIELD = "_connection";
        private const string CONNECTION_STRING_FIELD = "_connectionString";

        private static readonly IList<Type> _primitiveTypeList = new Type[]
        {
            typeof(string),
            typeof(byte),
            typeof(bool),
            typeof(UInt16),
            typeof(Int16),
            typeof(uint),
            typeof(int),
            typeof(UInt64),
            typeof(Int64),
            typeof(decimal),
            typeof(Single),
            typeof(double),
            typeof(Guid),
            typeof(DateTime)
        };

        /// <summary>Protected constructor.</summary>
        /// <param name="options"></param>
        protected SqlDacEmitter(SqlDacOptions options)
            : base(options)
        {
        }

        #region Factory Methods
        /// <summary>Creates a factory of DAC instance connecting with a connection string.</summary>
        /// <typeparam name="INTER">Interface type of the DAC.</typeparam>
        /// <param name="connectionStringName">Name of the connection string defined in the configuration file.</param>
        /// <returns>A factory of DAC instance.</returns>
        public static IFactory<INTER> GetFactoryUsingConnectionString<INTER>(string connectionStringName)
            where INTER : class
        {
            return GetFactoryUsingConnectionString<INTER, object>(connectionStringName);
        }

        /// <summary>Creates a factory of DAC instance connecting with a SQL connection.</summary>
        /// <typeparam name="INTER">Interface type of the DAC.</typeparam>
        /// <returns>A factory of DAC instance.</returns>
        public static IFactory<INTER, SqlConnection> GetFactoryUsingSqlConnection<INTER>()
            where INTER : class
        {
            return GetFactoryUsingSqlConnection<INTER, object>();
        }

        /// <summary>Creates a factory of DAC instance with a given base class connecting with a connection string.</summary>
        /// <typeparam name="INTER">Interface type of the DAC.</typeparam>
        /// <typeparam name="BASE">Type of the base class.</typeparam>
        /// <param name="connectionStringName">Name of the connection string defined in the configuration file.</param>
        /// <returns>A factory of DAC instance.</returns>
        public static IFactory<INTER> GetFactoryUsingConnectionString<INTER, BASE>(string connectionStringName)
            where INTER : class
            where BASE : class
        {
            SqlDacOptions options = new SqlDacOptions
            {
                ExposedInterfaceType = typeof(INTER),
                BaseType = typeof(BASE),
                ConnectionMode = ConnectionMode.ConnectionStringName,
                ConnectionStringName = connectionStringName
            };
            object untypedFactory = GetFactory(options);
            IFactory<INTER> factory = untypedFactory as IFactory<INTER>;

            if (factory == null)
            {
                throw new ApplicationException("Unable to cast the factory to the proper factory type");
            }

            return factory;
        }

        /// <summary>Creates a factory of DAC instance with a given base class connecting with a SQL connection.</summary>
        /// <typeparam name="INTER">Interface type of the DAC.</typeparam>
        /// <typeparam name="BASE">Type of the base class.</typeparam>
        /// <returns>A factory of DAC instance.</returns>
        public static IFactory<INTER, SqlConnection> GetFactoryUsingSqlConnection<INTER, BASE>()
            where INTER : class
            where BASE : class
        {
            SqlDacOptions options = new SqlDacOptions
            {
                ExposedInterfaceType = typeof(INTER),
                BaseType = typeof(BASE),
                ConnectionMode = ConnectionMode.ValidConnection
            };
            object untypedFactory = GetFactory(options);
            IFactory<INTER, SqlConnection> factory = untypedFactory as IFactory<INTER, SqlConnection>;

            if (factory == null)
            {
                throw new ApplicationException("Unable to cast the factory to the proper factory type");
            }

            return factory;
        }

        /// <summary>
        /// Creates a factory of DAC instance with a given base class taking a parameter in construction
        /// connecting with a connection string.
        /// </summary>
        /// <typeparam name="INTER">Interface type of the DAC.</typeparam>
        /// <typeparam name="BASE">Type of the base class.</typeparam>
        /// <typeparam name="BASE_CTOR_PARAM">Type of the base class constructor parameter.</typeparam>
        /// <param name="connectionStringName">Name of the connection string defined in the configuration file.</param>
        /// <returns>A factory of DAC instance.</returns>
        public static IFactory<INTER, BASE_CTOR_PARAM>
            GetFactoryUsingConnectionString<INTER, BASE, BASE_CTOR_PARAM>(string connectionStringName)
            where INTER : class
            where BASE : class
        {
            SqlDacOptions options = new SqlDacOptions
            {
                ExposedInterfaceType = typeof(INTER),
                BaseType = typeof(BASE),
                BaseConstructorParameterTypeList = new Type[] { typeof(BASE_CTOR_PARAM) },
                ConnectionMode = ConnectionMode.ConnectionStringName,
                ConnectionStringName = connectionStringName
            };
            object untypedFactory = GetFactory(options);
            IFactory<INTER, BASE_CTOR_PARAM> factory = untypedFactory as IFactory<INTER, BASE_CTOR_PARAM>;

            if (factory == null)
            {
                throw new ApplicationException("Unable to cast the factory to the proper factory type");
            }

            return factory;
        }

        /// <summary>
        /// Creates a factory of DAC instance with a given base class taking a parameter in construction
        /// connecting with a SQL connection.
        /// </summary>
        /// <typeparam name="INTER">Interface type of the DAC.</typeparam>
        /// <typeparam name="BASE">Type of the base class.</typeparam>
        /// <typeparam name="BASE_CTOR_PARAM">Type of the base class constructor parameter.</typeparam>
        /// <returns>A factory of DAC instance.</returns>
        public static IFactory<INTER, SqlConnection, BASE_CTOR_PARAM>
            GetFactoryUsingSqlConnection<INTER, BASE, BASE_CTOR_PARAM>()
            where INTER : class
            where BASE : class
        {
            SqlDacOptions options = new SqlDacOptions
            {
                ExposedInterfaceType = typeof(INTER),
                BaseType = typeof(BASE),
                BaseConstructorParameterTypeList = new Type[] { typeof(BASE_CTOR_PARAM) },
                ConnectionMode = ConnectionMode.ValidConnection
            };
            object untypedFactory = GetFactory(options);
            IFactory<INTER, SqlConnection, BASE_CTOR_PARAM> factory =
                untypedFactory as IFactory<INTER, SqlConnection, BASE_CTOR_PARAM>;

            if (factory == null)
            {
                throw new ApplicationException("Unable to cast the factory to the proper factory type");
            }

            return factory;
        }

        /// <summary>Creates a factory of DAC instance given options.</summary>
        /// <param name="options">Options dictating of factory to create.</param>
        /// <returns>A factory of DAC instance.</returns>
        public static object GetFactory(SqlDacOptions options)
        {
            if (options == null)
            {
                throw new ArgumentNullException("options");
            }

            SqlDacEmitter emitter = new SqlDacEmitter(options);
            object factory = options.Cache.GetFactory(emitter);

            return factory;
        }
        #endregion

        /// <summary>Emits a factory corresponding to the state of this emitter.</summary>
        /// <param name="componentTypeName"></param>
        /// <param name="factoryTypeName"></param>
        /// <param name="moduleBuilder"></param>
        /// <returns></returns>
        public override object EmitFactory(
            string componentTypeName,
            string factoryTypeName,
            ModuleBuilderExtender moduleBuilder)
        {
            TypeBuilderExtender typeBuilder = moduleBuilder.CreateType(
                componentTypeName,
                Options.BaseType,
                Options.ExposedInterfaceType);
            Type[] baseClassInterfaces = Options.IsUsingBaseClassInterfaces
                ? TypeHelper.GetAllInterfaces(Options.BaseType, true)
                : new Type[0];

            DefineCustomConstructors(typeBuilder);
            ImplementInterface(
                typeBuilder,
                Options.ExposedInterfaceType,
                null,
                null,
                null,
                null,
                EmitMethod,
                baseClassInterfaces);

            Type componentType = typeBuilder.TypeBuilder.CreateType();
            object factory =
                CreateFactoryInstance(factoryTypeName, componentType, Options.ExposedInterfaceType, moduleBuilder);

            return factory;
        }

        /// <summary>Returns <c>true</c> iif the given type is a simple (non-composite).</summary>
        /// <param name="type"></param>
        /// <returns></returns>
        protected virtual bool IsSimpleType(Type type)
        {
            Type underlyingValueType = TypeHelper.GetNullableUnderlyingType(type);

            if (underlyingValueType != null)
            {   //  Type is a nullable type
                return IsSimpleType(underlyingValueType);
            }
            else
            {   //  Type isn't a nullable type
                return _primitiveTypeList.Contains(type);
            }
        }

        #region Constructors
        private void DefineCustomConstructors(TypeBuilderExtender typeBuilder)
        {
            switch (Options.ConnectionMode)
            {
                case ConnectionMode.ConnectionStringName:
                    DefineCustomStaticConstructor(typeBuilder);
                    DefineCustomInstanceConstructorTakingNoConnection(typeBuilder);
                    break;
                case ConnectionMode.ValidConnection:
                    DefineCustomInstanceConstructorTakingConnection(typeBuilder);
                    break;
                default:
                    throw new NotSupportedException(
                        string.Format("Connection Mode not supported:  ", Options.ConnectionMode));
            }
        }

        private void DefineCustomStaticConstructor(TypeBuilderExtender typeBuilder)
        {
            FieldBuilder connectionStringField =
                typeBuilder.DefinePrivateField(CONNECTION_STRING_FIELD, typeof(string), true, true);
            //  Remove the '_' from the field name to get the parameter name
            string connectionParameterName = connectionStringField.Name.Substring(1);
            MethodParameter connectionParameter = new MethodParameter(connectionParameterName, connectionStringField.FieldType);
            ILGeneratorExtender constructorGenerator = typeBuilder.DefineStaticConstructor();
            LocalBuilder settings = constructorGenerator.DefineVariable(typeof(ConnectionStringSettings));
            Label foundSettingsLabel = constructorGenerator.DefineLabel();

            constructorGenerator.CallMethod(typeof(ConfigurationManager).GetMethod("get_ConnectionStrings"));
            constructorGenerator.StackValue(Options.ConnectionStringName);
            constructorGenerator.CallMethod(typeof(ConnectionStringSettingsCollection).GetMethod(
                "get_Item",
                new Type[] { typeof(string) }));
            constructorGenerator.StoreStackInVariable(settings);
            constructorGenerator.StackVariable(settings);
            constructorGenerator.IfNull(foundSettingsLabel);
            constructorGenerator.StackValue(
                string.Format("Connection string '{0}' isn't defined in configuration", Options.ConnectionStringName));
            constructorGenerator.StackNew(typeof(ApplicationException).GetConstructor(new Type[] { typeof(string) }));
            constructorGenerator.Throw();
            constructorGenerator.MarkLabel(foundSettingsLabel);
            constructorGenerator.StackVariable(settings);
            constructorGenerator.CallMethod(typeof(ConnectionStringSettings).GetMethod("get_ConnectionString"));
            constructorGenerator.StoreStackInField(connectionStringField);
            constructorGenerator.ReturnStack();
        }

        private void DefineCustomInstanceConstructorTakingConnection(TypeBuilderExtender typeBuilder)
        {
            FieldBuilder connectionField =
                typeBuilder.DefinePrivateField(CONNECTION_FIELD, typeof(SqlConnection), true, false);
            //  Remove the '_' from the field name to get the parameter name
            string connectionParameterName = connectionField.Name.Substring(1);
            MethodParameter connectionParameter = new MethodParameter(connectionParameterName, connectionField.FieldType);
            ILGeneratorExtender constructorGenerator = CreateInstanceConstructor(
                typeBuilder,
                Options.BaseType,
                Options.BaseConstructorParameterTypeList,
                connectionParameter);

            constructorGenerator.ValidateNotNullParameter(1, connectionParameterName);
            //  Store connection in instance field
            constructorGenerator.StackMethodParameter(0);
            constructorGenerator.StackMethodParameter(1);
            constructorGenerator.StoreStackInField(connectionField);
            constructorGenerator.ReturnStack();
        }

        private void DefineCustomInstanceConstructorTakingNoConnection(TypeBuilderExtender typeBuilder)
        {
            ILGeneratorExtender constructorGenerator = CreateInstanceConstructor(
                typeBuilder,
                Options.BaseType,
                Options.BaseConstructorParameterTypeList);

            constructorGenerator.ReturnStack();
        }
        #endregion

        private void EmitMethod(ILGeneratorExtender ilGenerator, MethodInfo method)
        {
            SprocMethodMapping methodMapping = new SprocMethodMapping(method, ilGenerator);

            ilGenerator.UsingBlock(
                delegate { return CreateTransactionScope(ilGenerator); },
                delegate(LocalBuilder scopeVar)
                {
                    EmitInnerUsingTransactionScope(ilGenerator, methodMapping);
                    ilGenerator.StackVariableReference(scopeVar);
                    ilGenerator.CallMethod(typeof(TransactionScope).GetMethod("Complete"));
                });
            methodMapping.Returns();
        }

        private void EmitInnerUsingTransactionScope(ILGeneratorExtender ilGenerator, SprocMethodMapping methodMapping)
        {
            LocalBuilder connectionVar = CreateConnection(ilGenerator);

            if (connectionVar != null)
            {
                ilGenerator.UsingBlock(connectionVar, delegate
                {
                    ilGenerator.StackVariableReference(connectionVar);
                    ilGenerator.CallMethod(typeof(SqlConnection).GetMethod("Open"));
                    EmitInnerUsingConnection(ilGenerator, methodMapping, connectionVar);
                });
            }
            else
            {   //  Make sure the connection is open
                Label connectionOpenLabel = ilGenerator.DefineLabel();

                ilGenerator.StackField(CONNECTION_FIELD);
                ilGenerator.CallMethod(typeof(SqlConnection).GetMethod("get_State"));
                ilGenerator.IfNotEqual((int)ConnectionState.Closed, connectionOpenLabel);
                ilGenerator.StackField(CONNECTION_FIELD);
                ilGenerator.CallMethod(typeof(SqlConnection).GetMethod("Open"));
                ilGenerator.MarkLabel(connectionOpenLabel);
                EmitInnerUsingConnection(ilGenerator, methodMapping, connectionVar);
            }
        }

        private void EmitInnerUsingConnection(
            ILGeneratorExtender ilGenerator,
            SprocMethodMapping methodMapping,
            LocalBuilder connectionVar)
        {
            ilGenerator.UsingBlock(
                delegate { return CreateCommand(ilGenerator, methodMapping, connectionVar); },
                delegate(LocalBuilder commandVar)
                {
                    ilGenerator.StackVariableReference(commandVar);
                    ilGenerator.StackValue((int)CommandType.StoredProcedure);
                    ilGenerator.CallMethod(typeof(SqlCommand).GetMethod("set_CommandType"));
                    SetupSprocParameters(ilGenerator, methodMapping, commandVar);
                    if (methodMapping.OutputParameters.Length > 0)
                    {
                        EmitUsingExecuteReader(ilGenerator, methodMapping, commandVar);
                    }
                    else
                    {
                        EmitExecuteNonReader(ilGenerator, commandVar);
                    }
                });
        }

        private void EmitExecuteNonReader(ILGeneratorExtender ilGenerator, LocalBuilder commandVar)
        {
            ilGenerator.StackVariable(commandVar);
            ilGenerator.CallMethod(typeof(SqlCommand).GetMethod("ExecuteNonQuery"));
            //  Get rid of the integer returned by execute non query
            ilGenerator.Pop();
        }

        private void EmitUsingExecuteReader(
            ILGeneratorExtender ilGenerator, SprocMethodMapping methodMapping, LocalBuilder commandVar)
        {
            ilGenerator.UsingBlock(
                delegate
                {
                    LocalBuilder readerVar = ilGenerator.DefineVariable(typeof(IDataReader));

                    ilGenerator.StackVariable(commandVar);
                    ilGenerator.CallMethod(typeof(SqlCommand).GetMethod("ExecuteReader", Type.EmptyTypes));
                    ilGenerator.StoreStackInVariable(readerVar);

                    return readerVar;
                },
                delegate(LocalBuilder readerVar)
                {
                    if (methodMapping.OutputParameters.Length != 1)
                    {
                        throw new NotSupportedException("Only one output parameter / return value is supported at the moment");
                    }
                    Type itemType = GetListItemType(methodMapping.OutputParameters[0].Type);

                    if (itemType != null)
                    {
                        LoadList(ilGenerator, readerVar, methodMapping.OutputParameters[0], itemType);
                    }
                    else
                    {
                        Label loadDoneLabel = ilGenerator.DefineLabel();

                        ilGenerator.StackVariable(readerVar);
                        ilGenerator.CallMethod(readerVar.LocalType.GetMethod("Read"));
                        ilGenerator.IfFalse(loadDoneLabel);
                        LoadSingleInstance(ilGenerator, readerVar, methodMapping.OutputParameters[0]);
                        ilGenerator.MarkLabel(loadDoneLabel);
                    }
                });
        }

        private void LoadSingleInstance(ILGeneratorExtender ilGenerator, LocalBuilder readerVar, IVariable instanceVariable)
        {
            if (IsSimpleType(instanceVariable.Type))
            {
                LoadSimpleType(ilGenerator, readerVar, null, instanceVariable.Type, null);
                instanceVariable.StoreIn();
            }
            else
            {
                LoadComplexType(ilGenerator, readerVar, instanceVariable);
            }
        }

        /// <summary>
        /// <paramref name="columnName"/> is the name of the column to access in the reader.
        /// If it's <c>null</c>, we just use the first column.
        /// </summary>
        /// <param name="ilGenerator"></param>
        /// <param name="readerVar"></param>
        /// <param name="columnName"></param>
        /// <param name="simpleType"></param>
        /// <param name="rawVar"></param>
        private void LoadSimpleType(
            ILGeneratorExtender ilGenerator,
            LocalBuilder readerVar,
            string columnName,
            Type simpleType,
            LocalBuilder rawVar)
        {
            MethodInfo readerGetItemMethod =
                columnName == null
                ? typeof(IDataRecord).GetMethod("get_Item", new Type[] { typeof(int) })
                : typeof(IDataRecord).GetMethod("get_Item", new Type[] { typeof(string) });
            Type underlyingValueType = TypeHelper.GetNullableUnderlyingType(simpleType);

            ilGenerator.StackVariableReference(readerVar);
            if (columnName == null)
            {
                ilGenerator.StackValue(0);
            }
            else
            {
                ilGenerator.StackValue(columnName);
            }
            ilGenerator.CallMethod(readerGetItemMethod);
            if (underlyingValueType != null)
            {
                EmitTransformDbNullToNullable(ilGenerator, readerVar, rawVar);
            }
            ilGenerator.Cast(simpleType);
        }

        /// <summary>We take <paramref name="rawVar"/> in order not to create too many variables.</summary>
        /// <param name="ilGenerator"></param>
        /// <param name="readerVar"></param>
        /// <param name="rawVar"></param>
        private void EmitTransformDbNullToNullable(
            ILGeneratorExtender ilGenerator, LocalBuilder readerVar, LocalBuilder rawVar)
        {
            Label notNullLabel = ilGenerator.DefineLabel();
            Label endIfLabel = ilGenerator.DefineLabel();

            if (rawVar == null)
            {
                rawVar = ilGenerator.DefineVariable(typeof(object));
            }
            ilGenerator.StoreStackInVariable(rawVar);
            ilGenerator.StackVariable(rawVar);
            ilGenerator.StackIsInstanceOf(typeof(DBNull));
            ilGenerator.IfFalse(notNullLabel);
            ilGenerator.StackNull();
            ilGenerator.Goto(endIfLabel);
            ilGenerator.MarkLabel(notNullLabel);
            ilGenerator.StackVariable(rawVar);
            ilGenerator.MarkLabel(endIfLabel);
        }

        private void LoadComplexType(ILGeneratorExtender ilGenerator, LocalBuilder readerVar, IVariable instanceVariable)
        {
            ConstructorInfo entityConstructor = instanceVariable.Type.GetConstructor(Type.EmptyTypes);
            MethodInfo readerGetItemMethod = typeof(IDataRecord).GetMethod("get_Item", new Type[] { typeof(string) });
            LocalBuilder rawVar = ilGenerator.DefineVariable(typeof(object));

            if (entityConstructor == null)
            {
                throw new ApplicationException(
                    string.Format("Type '{0}' doesn't have a default constructor", instanceVariable.Type.Name));
            }

            ilGenerator.StackNew(entityConstructor);
            instanceVariable.StoreIn();

            foreach (PropertyInfo property in instanceVariable.Type.GetProperties())
            {
                if (property.CanRead && property.CanWrite && IsSimpleType(property.PropertyType))
                {   //  entity.PropertyName = (PropertyType)reader["propertyName"];
                    instanceVariable.StackOut();
                    LoadSimpleType(ilGenerator, readerVar, property.Name, property.PropertyType, rawVar);
                    ilGenerator.CallMethod(property.GetSetMethod());
                }
            }
        }

        private void LoadList(ILGeneratorExtender ilGenerator, LocalBuilder readerVar, IVariable listVar, Type itemType)
        {
            Type tempListType = typeof(List<int>).GetGenericTypeDefinition().MakeGenericType(itemType);
            LocalBuilder tempListVar = ilGenerator.DefineVariable(tempListType);

            ilGenerator.StackNew(tempListType.GetConstructor(Type.EmptyTypes));
            ilGenerator.StoreStackInVariable(tempListVar);
            ilGenerator.While(
                delegate
                {
                    ilGenerator.StackVariable(readerVar);
                    ilGenerator.CallMethod(readerVar.LocalType.GetMethod("Read"));
                },
                delegate
                {
                    IVariable itemVar = new LocalVariable(ilGenerator, ilGenerator.DefineVariable(itemType));

                    LoadSingleInstance(ilGenerator, readerVar, itemVar);
                    ilGenerator.StackVariable(tempListVar);
                    itemVar.StackOut();
                    ilGenerator.CallMethod(tempListType.GetMethod("Add"));
                });
            ilGenerator.StackVariable(tempListVar);
            if (listVar.Type.IsArray)
            {   //  Do a ToArray to return the list
                ilGenerator.CallMethod(tempListType.GetMethod("ToArray"));
            }
            listVar.StoreIn();
        }

        private Type GetListItemType(Type listType)
        {
            Type[] interfaceTypeList = TypeHelper.GetAllInterfaces(listType, true);

            foreach (Type interfaceType in interfaceTypeList)
            {   //  Look for IList<.>
                if (interfaceType.IsGenericType
                    && interfaceType.GetGenericTypeDefinition() == typeof(IList<int>).GetGenericTypeDefinition())
                {
                    return interfaceType.GetGenericArguments()[0];
                }
            }

            return null;
        }

        private void SetupSprocParameters(
            ILGeneratorExtender ilGenerator,
            SprocMethodMapping methodMapping,
            LocalBuilder commandVar)
        {
            if (methodMapping.InputParameters.Length > 0)
            {
                LocalBuilder paramCollectionVar = ilGenerator.DefineVariable(typeof(SqlParameterCollection));

                ilGenerator.StackVariableReference(commandVar);
                ilGenerator.CallMethod(typeof(SqlCommand).GetMethod("get_Parameters"));
                ilGenerator.StoreStackInVariable(paramCollectionVar);

                for (int i = 0; i != methodMapping.InputParameters.Length; ++i)
                {
                    var parameter = methodMapping.InputParameters[i];
                    LocalBuilder paramVar = ilGenerator.DefineVariable(typeof(SqlParameter));
                    string paramName = string.Concat('@', parameter.Name);

                    //  Create parameter
                    ilGenerator.StackValue(paramName);
                    ilGenerator.StackMethodParameter(i + 1);
                    ilGenerator.BoxIfValueType(parameter.Type);
                    ilGenerator.StackNew(typeof(SqlParameter).GetConstructor(new Type[] { typeof(string), typeof(object) }));
                    ilGenerator.StoreStackInVariable(paramVar);
                    //  Setup Parameter
                    ilGenerator.StackVariable(paramVar);
                    ilGenerator.StackValue((int)GetDirection(parameter));
                    ilGenerator.CallMethod(typeof(SqlParameter).GetMethod("set_Direction"));
                    //   Add the parameter to the collection
                    ilGenerator.StackVariableReference(paramCollectionVar);
                    ilGenerator.StackVariableReference(paramVar);
                    ilGenerator.CallMethod(
                        typeof(SqlParameterCollection).GetMethod("Add", new Type[] { typeof(SqlParameter) }));
                    //  Get rid of the SqlParameter returned by add
                    ilGenerator.Pop();
                }
            }
        }

        private static ParameterDirection GetDirection(SprocMethodMapping.SprocParameterMapping parameter)
        {
            if (parameter.IsOut && !parameter.IsIn)
            {
                return ParameterDirection.Output;
            }
            else if (!parameter.IsOut && parameter.IsIn)
            {
                return ParameterDirection.Input;
            }
            else if (parameter.IsOut && parameter.IsIn)
            {
                return ParameterDirection.InputOutput;
            }
            else
            {
                return ParameterDirection.Input;
            }
        }

        private LocalBuilder CreateCommand(
            ILGeneratorExtender ilGenerator,
            SprocMethodMapping methodMapping,
            LocalBuilder connectionVar)
        {
            LocalBuilder commandVar = ilGenerator.DefineVariable(typeof(SqlCommand));

            ilGenerator.StackValue(methodMapping.Name);
            if (connectionVar != null)
            {
                ilGenerator.StackVariableReference(connectionVar);
            }
            else
            {
                ilGenerator.StackField(CONNECTION_FIELD);
            }
            ilGenerator.StackNew(typeof(SqlCommand).GetConstructor(new Type[] { typeof(string), typeof(SqlConnection) }));
            ilGenerator.StoreStackInVariable(commandVar);

            return commandVar;
        }

        private LocalBuilder CreateConnection(ILGeneratorExtender ilGenerator)
        {
            LocalBuilder connectionVar = null;

            if (Options.ConnectionMode == ConnectionMode.ConnectionStringName)
            {
                connectionVar = ilGenerator.DefineVariable(typeof(SqlConnection));

                ilGenerator.StackField(CONNECTION_STRING_FIELD);
                ilGenerator.StackNew(typeof(SqlConnection).GetConstructor(new Type[] { typeof(string) }));
                ilGenerator.StoreStackInVariable(connectionVar);
            }

            return connectionVar;
        }

        private LocalBuilder CreateTransactionScope(ILGeneratorExtender ilGenerator)
        {
            LocalBuilder scopeVar = ilGenerator.DefineVariable(typeof(TransactionScope));
            LocalBuilder transactionOptionsVar = ilGenerator.DefineVariable(typeof(TransactionOptions));
            ConstructorInfo transactionScopeConstructor = typeof(TransactionScope).GetConstructor(
                new Type[] { typeof(TransactionScopeOption), typeof(TransactionOptions) });

            //  Setup TransactionOptions
            ilGenerator.StackVariableReference(transactionOptionsVar);
            ilGenerator.InitValueType(typeof(TransactionOptions));
            ilGenerator.StackVariableReference(transactionOptionsVar);
            ilGenerator.StackValue((int)Options.IsolationLevel);
            ilGenerator.CallMethod(typeof(TransactionOptions).GetMethod("set_IsolationLevel"));

            ilGenerator.StackVariableReference(transactionOptionsVar);
            ilGenerator.StackValue(Options.TransactionTimeout.TotalMilliseconds);
            ilGenerator.CallMethod(typeof(TimeSpan).GetMethod("FromMilliseconds"));
            ilGenerator.CallMethod(typeof(TransactionOptions).GetMethod("set_Timeout"));

            //  Construct the scope
            ilGenerator.StackValue((int)Options.TransactionScopeOption);
            ilGenerator.StackVariableValue(transactionOptionsVar);
            ilGenerator.StackNew(transactionScopeConstructor);
            ilGenerator.StoreStackInVariable(scopeVar);

            return scopeVar;
        }
    }
}